﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using DBUtility;
using Model;
using MySql.Data.MySqlClient;

namespace DAL
{
    public class DTaxi
    {
        public bool ExistsPhoneNum(string phoneNumber)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from ttaxi");
            strSql.Append(" where PhoneNumber=@PhoneNumber");
            MySqlParameter[] parameters = {
					new MySqlParameter("@PhoneNumber", MySqlDbType.VarChar,20)
            };
            parameters[0].Value = phoneNumber;

            return DbHelperMySQL.Exists(strSql.ToString(), parameters);
        }

        public bool Add(TaxiInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into ttaxi(");
            strSql.Append("PhoneNumber,TaxiNo,TaxiType,State,");
            strSql.Append("CompanyId,Remark)");
            strSql.Append(" values (");
            strSql.Append("@PhoneNumber,@TaxiNo,@TaxiType,@State,");
            strSql.Append("@CompanyId,@Remark)");
            MySqlParameter[] parameters = {
					new MySqlParameter("@PhoneNumber", MySqlDbType.VarChar,15),
					new MySqlParameter("@TaxiNo", MySqlDbType.VarChar,20),
					new MySqlParameter("@TaxiType", MySqlDbType.VarChar,20),
					new MySqlParameter("@State", MySqlDbType.VarChar,10),               
                    new MySqlParameter("@CompanyId", MySqlDbType.Int32),
                    new MySqlParameter("@Remark", MySqlDbType.Text)};
            parameters[0].Value = model.PhoneNumber;
            parameters[1].Value = model.TaxiNo;
            parameters[2].Value = model.TaxiType;            
            parameters[3].Value = model.State;             
            parameters[4].Value = model.CompanyId;
            parameters[5].Value = model.Remark;
            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public bool Update(TaxiInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update ttaxi set ");
            strSql.Append("PhoneNumber=@PhoneNumber,");
            strSql.Append("TaxiNo=@TaxiNo,");
            strSql.Append("TaxiType=@TaxiType,");
            strSql.Append("CompanyId=@CompanyId,");
            strSql.Append("IMEI=@IMEI,");
            strSql.Append("State=@State,");
            strSql.Append("Remark=@Remark");
             
            strSql.Append(" where id=@id");
            MySqlParameter[] parameters = {
					new MySqlParameter("@PhoneNumber", MySqlDbType.VarChar,15),
					new MySqlParameter("@TaxiNo", MySqlDbType.VarChar,20),
					new MySqlParameter("@TaxiType", MySqlDbType.VarChar,20),
					new MySqlParameter("@CompanyId", MySqlDbType.Int32,10),
                    new MySqlParameter("@IMEI", MySqlDbType.VarChar,25),
                    new MySqlParameter("@State", MySqlDbType.VarChar,10),
					new MySqlParameter("@Remark", MySqlDbType.Text), 
					new MySqlParameter("@id", MySqlDbType.Int32,10)};
            parameters[0].Value = model.PhoneNumber;
            parameters[1].Value = model.TaxiNo;
            parameters[2].Value = model.TaxiType;
            parameters[3].Value = model.CompanyId;
            parameters[4].Value = model.Imei;
            parameters[5].Value = model.State;
            parameters[6].Value = model.Remark;
            parameters[7].Value = model.Id;
            
            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public bool UpdateState(string phoneNumber, int state)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update ttaxi set ");
            strSql.Append("State=@State");
            strSql.Append(" where PhoneNumber=@PhoneNumber");
            MySqlParameter[] parameters = {					 
					new MySqlParameter("@State", MySqlDbType.VarChar,10),					
					new MySqlParameter("@PhoneNumber", MySqlDbType.VarChar,15)};
            parameters[0].Value = state;
            parameters[1].Value = phoneNumber;

            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public bool UpdateImei(string phoneNumber, string imei)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update ttaxi set ");
            strSql.Append("Imei=@Imei");
            strSql.Append(" where PhoneNumber=@PhoneNumber");
            MySqlParameter[] parameters = {					 
					new MySqlParameter("@Imei", MySqlDbType.VarChar,25),					
					new MySqlParameter("@PhoneNumber", MySqlDbType.VarChar,15)};
            parameters[0].Value = imei;
            parameters[1].Value = phoneNumber;

            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
     
        public bool Delete(int id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from ttaxi ");
            strSql.Append(" where id=@id");
            MySqlParameter[] parameters = {
					new MySqlParameter("@id", MySqlDbType.Int32)
};
            parameters[0].Value = id;

            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public TaxiInfo GetModelByPhoneNum(string phoneNumber)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  t.Id,t.PhoneNumber,t.TaxiNo,t.TaxiType,");
            strSql.Append("t.State,t.CompanyId,t.Remark,");
            strSql.Append("tb.CompanyName ");
            strSql.Append("from ttaxi t left join tcompany tb on ");
            strSql.Append("t.CompanyId = tb.Id");
            strSql.Append(" where t.PhoneNumber =@PhoneNumber");
            MySqlParameter[] parameters = {
					new MySqlParameter("@PhoneNumber", MySqlDbType.VarChar)};
            parameters[0].Value = phoneNumber;

            TaxiInfo model = new Model.TaxiInfo();
            DataSet ds = DbHelperMySQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["Id"] != null && ds.Tables[0].Rows[0]["Id"].ToString() != "")
                {
                    model.Id = int.Parse(ds.Tables[0].Rows[0]["Id"].ToString());
                }
                if (ds.Tables[0].Rows[0]["PhoneNumber"] != null && ds.Tables[0].Rows[0]["PhoneNumber"].ToString() != "")
                {
                    model.PhoneNumber = ds.Tables[0].Rows[0]["PhoneNumber"].ToString();
                }
                if (ds.Tables[0].Rows[0]["TaxiNo"] != null && ds.Tables[0].Rows[0]["TaxiNo"].ToString() != "")
                {
                    model.TaxiNo = ds.Tables[0].Rows[0]["TaxiNo"].ToString();
                }
                if (ds.Tables[0].Rows[0]["TaxiType"] != null && ds.Tables[0].Rows[0]["TaxiType"].ToString() != "")
                {
                    model.TaxiType = ds.Tables[0].Rows[0]["TaxiType"].ToString();
                }
              
                if (ds.Tables[0].Rows[0]["State"] != null && ds.Tables[0].Rows[0]["State"].ToString() != "")
                {
                    model.State = ds.Tables[0].Rows[0]["State"].ToString();
                }
          
                if (ds.Tables[0].Rows[0]["CompanyId"] != null && ds.Tables[0].Rows[0]["CompanyId"].ToString() != "")
                {
                    model.CompanyId = int.Parse(ds.Tables[0].Rows[0]["CompanyId"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Remark"] != null && ds.Tables[0].Rows[0]["Remark"].ToString() != "")
                {
                    model.Remark = ds.Tables[0].Rows[0]["Remark"].ToString();
                }
          
                if (ds.Tables[0].Rows[0]["CompanyName"] != null && ds.Tables[0].Rows[0]["CompanyName"].ToString() != "")
                {
                    model.CompanyName = ds.Tables[0].Rows[0]["CompanyName"].ToString();
                }
                return model;
            }
            else
            {
                return null;
            }
        }

        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  t.Id,t.PhoneNumber,t.TaxiNo,t.TaxiType,");
            strSql.Append("t.State,t.CompanyId,t.Remark,t.IMEI,");
            strSql.Append("tb.CompanyName ");
            strSql.Append("from ttaxi t left join tcompany tb on ");
            strSql.Append("t.CompanyId = tb.Id ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperMySQL.Query(strSql.ToString());
        }
         
    }
}
